 /*********************************************************************************
 *      Copyright:  (C) HuangXiangming
 *                  All rights reserved.
 *
 *       Filename:  sqlite_server.c
 *    Description:  This file is Client database-related functions 
 *                 
 *        Version:  1.0.0(25/04/03)
 *         Author:  Huang Xiangming <huangxiangming@gmail.com>
 *      ChangeLog:  1, Release initial version on "25/04/03 13:10:30"
 *                 
 ********************************************************************************/
#include <ctype.h>
#include <unistd.h>
#include <stdio.h>
#include <stdlib.h>
#include <signal.h>
#include <string.h>
#include <errno.h>
#include <sys/types.h>

#include "sqlite3.h"
#include "sqlite_client.h"
#include "logger.h"
#include "get_data.h"

#define DB_NAME		"ds18b20.db"

static sqlite3 				*db;
static int					table_exist = 0;
static char					buf[1024];
static int 					callback_count = 0;

//数据库初始化（创建、打开）
int sqlite_init(char *table_name)
{
	int					rv = -1;
	
	rv = sqlite3_open(DB_NAME, &db);
	if ( rv != SQLITE_OK )
	{
		log_error("open database[%s] error!\n", DB_NAME);
		goto Cleanup;
	}
	log_info("open database[%s] successfully!\n", DB_NAME);
	
	if ( sqlite_table(table_name) < 0 )
	{
		goto Cleanup;
	}

	return 0;

Cleanup:
	sqlite_close();
	return -1;
}

//关闭数据库
void sqlite_close(void)
{	
	int					rv = -1;
	
	rv = sqlite3_close(db);
	return ;
}

//查询表
int sqlite_table(char *table_name)
{
	char					query[512];
	int						rv = -1;
	char	 				*sqlError = NULL;
	
	sprintf(query, "SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='%s';", table_name);
	rv = sqlite3_exec(db, query, table_callback, NULL, &sqlError);
	if ( rv != SQLITE_OK )
	{
		log_warn("Failed to query the table: %s\n", sqlError);
		goto Cleanup;
	}

	if ( table_exist == 0 )		//表存在返回1，不存在返回0
	{
		sprintf(query, "CREATE TABLE [%s] (ID CHAR(10), TIME CHAR(10), TEMPERATURE FLOAT);", table_name);
		rv = sqlite3_exec(db, query, NULL, NULL, &sqlError);
		if ( rv != SQLITE_OK )
		{
			log_error("Failed to create the table: %s\n", sqlError);
			goto Cleanup;
		}
		printf("Create table successfully!\n");
	}
	return 0;

Cleanup:
	sqlite3_free(sqlError);
	sqlite_close();
	return -1;
}

//查询表回调函数
static int table_callback(void *exec_para, int n_column, char **column_value, char **column_name)
{
	if ( 1 == n_column )
	{
		table_exist = atoi(*(column_value));
	}

	return 0;
}

//插入数据
int sqlite_inset(pack_t *data)
{
	char				sql_inset[512];			//存储SQL语句
	int					rv = -1;
	char 				*sqlError = NULL;

	sprintf(sql_inset, "INSERT INTO %s VALUES ('%s', '%s', %.4f);", data->id, data->id, data->sample_time, data->temp);
	rv = sqlite3_exec(db, sql_inset, NULL, NULL, &sqlError);
	if ( rv != SQLITE_OK )
	{
		log_warn("Inset data into table failure: %s\n", sqlError);
		sqlite3_free(sqlError);
		return -1;
	}
	else
	{
		log_info("Inset data into table successfully!\n");
	}

	return 0;
}


//获取第一条数据
int get_database_1st_record(pack_t *data)
{
	char            record_query[512];      //存储SQL语句
	int				rv = -1;
	char            *sqlError = NULL;

	memset(record_query, 0, sizeof(record_query));
	sprintf(record_query, "SELECT * FROM [%s] limit 1 offset 0;", data->id);
	rv = sqlite3_exec(db, record_query, record_query_callback, data, &sqlError);
	if ( rv != SQLITE_OK )
	{   
		log_warn("Failed to select data from table: %s\n", sqlError);
		sqlite3_free(sqlError);
		return -1;
	}

	//判断是否有数据
	if (callback_count == 0) 
	{
		return 0;
	}

	callback_count = 0;

	return 1;
}

static int record_query_callback(void *exec_para, int n_column, char **column_value, char **column_name)
{
	pack_t 					*data = (pack_t *)exec_para;
	float                   temp;

	callback_count++;

	temp = strtof(column_value[2], NULL);
	strncpy(data->sample_time, column_value[1], sizeof(data->sample_time) - 1);
	data->temp = temp;

	return 0;
}

//删除数据
int delete_record(pack_t *data)
{
	char            delete_query[512];
	int             rv = -1;
	char            *sqlError = NULL;

	memset(delete_query, 0, sizeof(delete_query));
	sprintf(delete_query, "DELETE FROM [%s] WHERE TIME='%s';", data->id, data->sample_time);

	rv = sqlite3_exec(db, delete_query, NULL, NULL, &sqlError);
	if (rv != SQLITE_OK)
	{
		log_warn("delect data failure: %s\n", sqlError);
		sqlite3_free(sqlError);
		return -1;
	}

	return 0;
}

